package com.bds.wm.service;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.bds.wm.model.procmodel.*;
import org.apache.log4j.Logger;
import org.nutz.dao.Sqls;
import org.nutz.dao.impl.NutDao;
import org.nutz.dao.sql.Sql;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * Created by guoyu on 2018/5/16.
 */
@Service
public class OverviewService {
    private static Logger LOGGER = Logger.getLogger(OverviewService.class);

    @Resource
    private NutDao dao;

    public OverviewResult getOverviewResult(int startMonth, int endMonth, String cityTier) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResult @StartMonth,@EndMonth,@CityTier");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        dao.execute(sql);

        return sql.getObject(OverviewResult.class);
    }

    public List<OverviewResultYoyMom> getOverviewRateResult(int startMonth, int endMonth, String cityTier) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultTest @StartMonth,@EndMonth,@Segment");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultYoyMom.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("Segment", cityTier); //设置入参
        dao.execute(sql);

        return sql.getList(OverviewResultYoyMom.class);
    }

    public OverviewResultRate getOverviewRateResult2(int startMonth, int endMonth, String cityTier) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultRate @StartMonth,@EndMonth,@CityTier");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultRate.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        dao.execute(sql);

        return sql.getObject(OverviewResultRate.class);
    }

    public JSONObject getOverviewResultBySubCategory(int startMonth, int endMonth, String cityTier, String categoryTier) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbySubCategory @StartMonth,@EndMonth,@CityTier,@CategoryTier");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultBySubCategory.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        sql.params().set("CategoryTier", categoryTier); //设置入参

        dao.execute(sql);

        JSONObject jsonObject = null;
        try {
            jsonObject = new JSONObject();
            List<OverviewResultBySubCategory> overviewResultBySubCategoryList = sql.getList(OverviewResultBySubCategory.class);
            if (categoryTier.equals("1")) {
                Map<String, Double> subCategoryGMVMap = new TreeMap<>();
                Map<String, Double> subCategoryTCMap = new TreeMap<>();
                Map<String, Double> subCategoryStoreMap = new TreeMap<>();
                for (OverviewResultBySubCategory c : overviewResultBySubCategoryList) {
                    //String cateName = c.getSubCategoryName();
                    String cateName = c.getCategoryName();
                    subCategoryGMVMap.put(cateName, c.getSubCategoryGMVPer());
                    subCategoryTCMap.put(cateName, c.getSubCategoryTCPer());
                    subCategoryStoreMap.put(cateName, c.getSubCategoryStorePer());
                }

                //将map.entrySet()转换成list
                List<Map.Entry<String, Double>> subCategoryGMVList
                        = new ArrayList<Map.Entry<String, Double>>(subCategoryGMVMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryGMVList, new Comparator<Map.Entry<String, Double>>() {
                    //降序排序
                    public int compare(Map.Entry<String, Double> o1,
                                       Map.Entry<String, Double> o2) {
                        return o2.getValue().compareTo(o1.getValue());
                    }
                });
                //将map.entrySet()转换成list
                List<Map.Entry<String, Double>> subCategoryTCList
                        = new ArrayList<Map.Entry<String, Double>>(subCategoryTCMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryTCList, new Comparator<Map.Entry<String, Double>>() {
                    //降序排序
                    public int compare(Map.Entry<String, Double> o1,
                                       Map.Entry<String, Double> o2) {
                        return o2.getValue().compareTo(o1.getValue());
                    }
                });
                //将map.entrySet()转换成list
                List<Map.Entry<String, Double>> subCategoryStoreList
                        = new ArrayList<Map.Entry<String, Double>>(subCategoryStoreMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryStoreList, new Comparator<Map.Entry<String, Double>>() {
                    //降序排序
                    public int compare(Map.Entry<String, Double> o1,
                                       Map.Entry<String, Double> o2) {
                        return o2.getValue().compareTo(o1.getValue());
                    }
                });

                JSONArray subCategoryGMVArray = new JSONArray();
                for (Map.Entry<String, Double> map : subCategoryGMVList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue());
                    subCategoryGMVArray.add(tmp);
                }
                JSONArray subCategoryTCArray = new JSONArray();
                for (Map.Entry<String, Double> map : subCategoryTCList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue());
                    subCategoryTCArray.add(tmp);
                }
                JSONArray subCategoryStoreArray = new JSONArray();
                for (Map.Entry<String, Double> map : subCategoryStoreList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue());
                    subCategoryStoreArray.add(tmp);
                }
                jsonObject.put("subCategoryGMV", subCategoryGMVArray);
                jsonObject.put("subCategoryTC", subCategoryTCArray);
                jsonObject.put("subCategoryStore", subCategoryStoreArray);
            } else {
                Map<String, OverviewResultBySubCategory> subCategoryGMVMap = new TreeMap<>();
                Map<String, OverviewResultBySubCategory> subCategoryTCMap = new TreeMap<>();
                Map<String, OverviewResultBySubCategory> subCategoryStoreMap = new TreeMap<>();
                for (OverviewResultBySubCategory c : overviewResultBySubCategoryList) {
                    String cateName = c.getSubCategoryName();
                    //String cateName = c.getCategoryName();
                    subCategoryGMVMap.put(cateName, c);
                    subCategoryTCMap.put(cateName, c);
                    subCategoryStoreMap.put(cateName, c);
                }

                //将map.entrySet()转换成list
                List<Map.Entry<String, OverviewResultBySubCategory>> subCategoryGMVList
                        = new ArrayList<Map.Entry<String, OverviewResultBySubCategory>>(subCategoryGMVMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryGMVList, new Comparator<Map.Entry<String, OverviewResultBySubCategory>>() {
                    //降序排序
                    public int compare(Map.Entry<String, OverviewResultBySubCategory> o1,
                                       Map.Entry<String, OverviewResultBySubCategory> o2) {
                        return o2.getValue().getSubCategoryGMVPer().compareTo(o1.getValue().getSubCategoryGMVPer());
                    }
                });
                //将map.entrySet()转换成list
                List<Map.Entry<String, OverviewResultBySubCategory>> subCategoryTCList
                        = new ArrayList<Map.Entry<String, OverviewResultBySubCategory>>(subCategoryTCMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryTCList, new Comparator<Map.Entry<String, OverviewResultBySubCategory>>() {
                    //降序排序
                    public int compare(Map.Entry<String, OverviewResultBySubCategory> o1,
                                       Map.Entry<String, OverviewResultBySubCategory> o2) {
                        return o2.getValue().getSubCategoryTCPer().compareTo(o1.getValue().getSubCategoryTCPer());
                    }
                });
                //将map.entrySet()转换成list
                List<Map.Entry<String, OverviewResultBySubCategory>> subCategoryStoreList
                        = new ArrayList<Map.Entry<String, OverviewResultBySubCategory>>(subCategoryStoreMap.entrySet());
                //通过比较器来实现排序
                Collections.sort(subCategoryStoreList, new Comparator<Map.Entry<String, OverviewResultBySubCategory>>() {
                    //降序排序
                    public int compare(Map.Entry<String, OverviewResultBySubCategory> o1,
                                       Map.Entry<String, OverviewResultBySubCategory> o2) {
                        return o2.getValue().getSubCategoryStorePer().compareTo(o1.getValue().getSubCategoryStorePer());
                    }
                });

                JSONArray subCategoryGMVArray = new JSONArray();
                for (Map.Entry<String, OverviewResultBySubCategory> map : subCategoryGMVList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue().getSubCategoryGMVPer());
                    tmp.put("first_cate_name", map.getValue().getCategoryName());
                    subCategoryGMVArray.add(tmp);
                }
                JSONArray subCategoryTCArray = new JSONArray();
                for (Map.Entry<String, OverviewResultBySubCategory> map : subCategoryTCList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue().getSubCategoryTCPer());
                    tmp.put("first_cate_name", map.getValue().getCategoryName());
                    subCategoryTCArray.add(tmp);
                }
                JSONArray subCategoryStoreArray = new JSONArray();
                for (Map.Entry<String, OverviewResultBySubCategory> map : subCategoryStoreList) {
                    JSONObject tmp = new JSONObject();
                    tmp.put("cate_name", map.getKey());
                    tmp.put("value", map.getValue().getSubCategoryStorePer());
                    tmp.put("first_cate_name", map.getValue().getCategoryName());
                    subCategoryStoreArray.add(tmp);
                }
                jsonObject.put("subCategoryGMV", subCategoryGMVArray);
                jsonObject.put("subCategoryTC", subCategoryTCArray);
                jsonObject.put("subCategoryStore", subCategoryStoreArray);
            }
        } catch (Exception e) {
            LOGGER.warn("[service]-[33]-合并getOverviewResultBySubCategory数据异常", e);
        }


        return jsonObject;
    }

    public List<OverviewResultBySubCategoryTrend> getOverviewResultBySubCategoryTrend(int startMonth, int endMonth,
                                                                                      String city_tier, int categoryTier,
                                                                                      String category_name) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbySubCategoryTrend @EndMonth," +
                "@CityTier,@CategoryTier,@CategoryName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultBySubCategoryTrend.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", city_tier); //设置入参
        sql.params().set("CategoryTier", categoryTier); //设置入参
        sql.params().set("CategoryName", category_name); //设置入参
        dao.execute(sql);
        return sql.getList(OverviewResultBySubCategoryTrend.class);
    }

    public JSONObject getOverviewResultByChannel(int startMonth, int endMonth, String cityTier) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyChannelTest @StartMonth,@EndMonth,@CityTier");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyChannelTopCity.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier);
        dao.execute(sql);
        List<OverviewResultbyChannelTopCity> overviewResultByChannelList1 = sql.getList(OverviewResultbyChannelTopCity.class);
        JSONObject json = new JSONObject(true);
        json.put("channelGMVper", new JSONObject(true));
        json.put("channelTCper", new JSONObject(true));
        json.put("channelStoreNoper", new JSONObject(true));
        for (OverviewResultbyChannelTopCity over : overviewResultByChannelList1) {
            json.getJSONObject("channelGMVper").put(over.getSubSegment(), over.getCityChannelGMV());
            json.getJSONObject("channelTCper").put(over.getSubSegment(), over.getCityChannelTC());
            json.getJSONObject("channelStoreNoper").put(over.getSubSegment(), over.getCityChannelStoreNo());
        }
        return json;
    }

    public JSONObject getOverviewResultByCity(int startMonth, int endMonth, String cityTier) {
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultbyChannelTopCity @StartMonth,@EndMonth,@CityTier");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultbyChannelTopCity.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier);
        dao.execute(sql);
        JSONObject json = null;
        try {
            List<OverviewResultbyChannelTopCity> overviewResultByChannelList = sql.getList(OverviewResultbyChannelTopCity.class);
            json = new JSONObject(true);
            json.put("cityGMVper", new JSONObject(true));
            json.put("cityTCper", new JSONObject(true));
            json.put("cityStoreNoper", new JSONObject(true));
            json.put("cityGMVTotal", new JSONArray());
            json.put("cityTCTotal", new JSONArray());
            json.put("cityStoreNoTotal", new JSONArray());
            JSONObject cityGMVperjs = new JSONObject();
            JSONObject cityTCperjs = new JSONObject();
            JSONObject cityStoreNoperjs = new JSONObject();
            JSONObject cityGMVTotaljs = new JSONObject();
            JSONObject cityTCTotaljs = new JSONObject();
            JSONObject cityStoreNoTotaljs = new JSONObject();
            Set<String> set = new HashSet<>();
            for (OverviewResultbyChannelTopCity over : overviewResultByChannelList) {
                String cityName = over.getSegment();
                String channel = over.getSubSegment();
                Double cityGMV = over.getCityChannelGMV();
                Double cityCityTC = over.getCityChannelTC();
                Double channelStoreNo = over.getCityChannelStoreNo();
                set.add(channel);
                if (cityGMVperjs.containsKey(channel)) {
                    cityGMVperjs.getJSONObject(channel).put(cityName, cityGMV);
                } else {
                    JSONObject js = new JSONObject();
                    js.put(cityName, cityGMV);
                    cityGMVperjs.put(channel, js);
                }
                if (cityTCperjs.containsKey(channel)) {
                    cityTCperjs.getJSONObject(channel).put(cityName, cityCityTC);
                } else {
                    JSONObject js = new JSONObject();
                    js.put(cityName, cityCityTC);
                    cityTCperjs.put(channel, js);
                }
                if (cityStoreNoperjs.containsKey(channel)) {
                    cityStoreNoperjs.getJSONObject(channel).put(cityName, channelStoreNo);
                } else {
                    JSONObject js = new JSONObject();
                    js.put(cityName, channelStoreNo);
                    cityStoreNoperjs.put(channel, js);
                }
                if (cityGMVTotaljs.containsKey(cityName)) {
                    cityGMVTotaljs.put(cityName, cityGMVTotaljs.getDouble(cityName) + cityGMV);
                } else {
                    cityGMVTotaljs.put(cityName, cityGMV);
                }
                if (cityTCTotaljs.containsKey(cityName)) {
                    cityTCTotaljs.put(cityName, cityTCTotaljs.getDouble(cityName) + cityCityTC);
                } else {
                    cityTCTotaljs.put(cityName, cityCityTC);
                }
                if (cityStoreNoTotaljs.containsKey(cityName)) {
                    cityStoreNoTotaljs.put(cityName, cityStoreNoTotaljs.getDouble(cityName) + channelStoreNo);
                } else {
                    cityStoreNoTotaljs.put(cityName, channelStoreNo);
                }
            }
            Map map = (Map) cityStoreNoTotaljs;
            Map map1 = (Map) cityTCTotaljs;
            Map map2 = (Map) cityGMVTotaljs;
            List<Map.Entry<String, Double>> list = new ArrayList<Map.Entry<String, Double>>(map.entrySet());
            List<Map.Entry<String, Double>> list1 = new ArrayList<Map.Entry<String, Double>>(map1.entrySet());
            List<Map.Entry<String, Double>> list2 = new ArrayList<Map.Entry<String, Double>>(map2.entrySet());
            // 然后通过比较器来实现排序
            Collections.sort(list, new Comparator<Map.Entry<String, Double>>() {
                @Override
                public int compare(Map.Entry<String, Double> o1, Map.Entry<String, Double> o2) {
                    // return 0;  // 降序
                    // return o2.getValue().compareTo(o1.getValue()); // 降序
                    return o1.getValue().compareTo(o2.getValue()); // 升序
                }
            });
            Collections.sort(list1, new Comparator<Map.Entry<String, Double>>() {
                @Override
                public int compare(Map.Entry<String, Double> o1, Map.Entry<String, Double> o2) {
                    // return 0;  // 降序
                    // return o2.getValue().compareTo(o1.getValue()); // 降序
                    return o1.getValue().compareTo(o2.getValue()); // 升序
                }
            });
            Collections.sort(list2, new Comparator<Map.Entry<String, Double>>() {
                @Override
                public int compare(Map.Entry<String, Double> o1, Map.Entry<String, Double> o2) {
                    // return 0;  // 降序
                    // return o2.getValue().compareTo(o1.getValue()); // 降序
                    return o1.getValue().compareTo(o2.getValue()); // 升序
                }
            });
            for (String str : set) {
                json.getJSONObject("cityGMVper").put(str, new JSONObject());
                json.getJSONObject("cityTCper").put(str, new JSONObject());
                json.getJSONObject("cityStoreNoper").put(str, new JSONObject());
            }
            for (Map.Entry<String, Double> mapping : list) {
                JSONObject js1 = new JSONObject();
                js1.put("city", mapping.getKey());
                js1.put("value", cityStoreNoTotaljs.getDouble(mapping.getKey()));
                json.getJSONArray("cityStoreNoTotal").add(js1);
                for (String str : set) {
                    json.getJSONObject("cityStoreNoper").getJSONObject(str).put(mapping.getKey(), cityStoreNoperjs.getJSONObject(str).getDouble(mapping.getKey()));
                }
            }
            for (Map.Entry<String, Double> mapping : list1) {
                JSONObject js2 = new JSONObject();
                js2.put("city", mapping.getKey());
                js2.put("value", cityTCTotaljs.getDouble(mapping.getKey()));
                json.getJSONArray("cityTCTotal").add(js2);
                for (String str : set) {
                    json.getJSONObject("cityTCper").getJSONObject(str).put(mapping.getKey(), cityTCperjs.getJSONObject(str).getDouble(mapping.getKey()));
                }
            }
            for (Map.Entry<String, Double> mapping : list2) {
                JSONObject js3 = new JSONObject();
                js3.put("city", mapping.getKey());
                js3.put("value", cityGMVTotaljs.getDouble(mapping.getKey()));
                json.getJSONArray("cityGMVTotal").add(js3);
                for (String str : set) {
                    json.getJSONObject("cityGMVper").getJSONObject(str).put(mapping.getKey(), cityGMVperjs.getJSONObject(str).getDouble(mapping.getKey()));
                }
            }
        } catch (Exception e) {
            LOGGER.warn("[service]-[33]-合并getOverviewResultByChannel数据异常", e);
        }
        String str = JSON.toJSONString(json);
        return json;
    }

    public JSONObject getMonthlyResultBychannel(int startMonth, int endMonth, String cityTier, int monthNum) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getMonthlyResultbyChannel @EndMonth,@Months,@Segment");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(MonthlyResultByChannel.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("Months", monthNum); //设置入参
        sql.params().set("Segment", cityTier); //设置入参
        dao.execute(sql);
        JSONObject jsonObject = null;
        try {
            List<MonthlyResultByChannel> monthlyResultByChannelList = sql.getList(MonthlyResultByChannel.class);
            jsonObject = new JSONObject();
            jsonObject.put("BaiduWaiMai", new JSONArray());
            jsonObject.put("ELeMe", new JSONArray());
            jsonObject.put("MeiTuan", new JSONArray());
            //按平台维度合并数据
            for (MonthlyResultByChannel m : monthlyResultByChannelList) {
                JSONObject tmp = new JSONObject();
                Long yearMonthTmp = m.getYearMonth();
                Double channelGMV = m.getChannelGMV();
                Double channelTC = m.getChannelTC();
                Double channelStoreNo = m.getChannelStoreNo();
                Long tierId = m.getTierID();
                String channelName = m.getSubSegment();

                tmp.put("yearMonth", yearMonthTmp);
                tmp.put("tierID", tierId);
                tmp.put("channelGMV", channelGMV);
                tmp.put("channelTC", channelTC);
                tmp.put("channelStoreNo", channelStoreNo);
                if (channelName.equals("BaiduWaiMai")) {
                    jsonObject.getJSONArray("BaiduWaiMai").add(tmp);
                } else if (channelName.equals("ELeMe")) {
                    jsonObject.getJSONArray("ELeMe").add(tmp);
                } else {
                    jsonObject.getJSONArray("MeiTuan").add(tmp);
                }
            }
        } catch (Exception e) {
            LOGGER.warn("[service]-[33]-合并getMonthlyResultBychannel数据异常", e);
        }

        return jsonObject;
    }

    public List<TopBrandOverviewResult> getTopBrandOverviewResult(int startMonth, int endMonth, String cityName,
                                                                  String categoryName, String cityTier) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getTopBrandOverviewResult @StartMonth,@EndMonth," +
                "@CityTier,@CityName,@CategoryName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(TopBrandOverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        sql.params().set("CityName", cityName); //设置入参
        sql.params().set("CategoryName", categoryName); //设置入参
        dao.execute(sql);
        return sql.getList(TopBrandOverviewResult.class);
    }

    public List<MapOverviewResult> getMapOverviewResult(int startMonth, int endMonth, String brandName,
                                                        double maxLng, double minLng, double maxLat, double minLat) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getMapOverviewResult @StartMonth,@EndMonth,@BrandName," +
                "@MaxLongitude,@MinLongitude,@MaxLatitude,@MinLatitude");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(MapOverviewResult.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        sql.params().set("MaxLongitude", maxLng); //设置入参
        sql.params().set("MinLongitude", minLng); //设置入参
        sql.params().set("MaxLatitude", maxLat); //设置入参
        sql.params().set("MinLatitude", minLat); //设置入参
        dao.execute(sql);
        return sql.getList(MapOverviewResult.class);
    }

    public List<MapOverviewCapacity> getMapOverviewResultByCapacity(int startMonth, int endMonth, String brandName,
                                                                    float realLng, float realLat) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getMapOverviewResultbyCapacity @EndMonth,@BrandName,@lon,@lat");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(MapOverviewCapacity.class));
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("BrandName", brandName); //设置入参
        sql.params().set("lon", realLng); //设置入参
        sql.params().set("lat", realLat); //设置入参
        dao.execute(sql);
        return sql.getList(MapOverviewCapacity.class);
    }

    public OverviewResultRateOfSub getOverviewResultRateOfSub(int startMonth, int endMonth,
                                                              String cityTier, String subCategory) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultRateofSub @StartMonth,@EndMonth," +
                "@CityTier,@SubCategory");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultRateOfSub.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        sql.params().set("SubCategory", subCategory); //设置入参
        dao.execute(sql);

        return sql.getObject(OverviewResultRateOfSub.class);
    }

    public List<OverviewResultOfSub> getOverviewResultOfSub(int startMonth, int endMonth,
                                                            String cityTier, String subCategory) {
        //创建SQL对象,用于调用存储过程
        Sql sql = Sqls.fetchEntity("exec Report.getOverviewResultofSub @StartMonth,@EndMonth," +
                "@CityTier,@SubCategoryName");
        sql.setCallback(Sqls.callback.entities());
        sql.setEntity(dao.getEntity(OverviewResultOfSub.class));
        sql.params().set("StartMonth", startMonth); //设置入参
        sql.params().set("EndMonth", endMonth); //设置入参
        sql.params().set("CityTier", cityTier); //设置入参
        sql.params().set("SubCategoryName", subCategory); //设置入参
        dao.execute(sql);

        return sql.getList(OverviewResultOfSub.class);
    }
}
